<?php
	
	
class M_student_payment_record Extends MY_Model{
	
		protected $_table = 'student_payment_records';
		private $enrollments_table = 'enrollments';
		private $last_input_id;
		
		public function __construct()
		{
			parent::__construct();
		}
		
		public function add_payment_record($data)
		{
			$sql = "SELECT max(spr_payment_number)as current_payment_number 
					FROM student_payment_records 
					WHERE spr_enrollment_id = ?
					AND spr_is_deleted = 0 ";
	
			$query = $this->db->query($sql,array($data['spr_enrollment_id']));
			if($query->num_rows() >= 1)
			{
				$result = $query->row()->current_payment_number;
				if($result == NULL)
				{
					$data['spr_payment_number'] = 1;
					$this->db->insert($this->_table,$data);
					$this->set_last_input_id($data);
					$this->insert_journal_entry($data); // insert to accounting
					return $this->db->affected_rows() > 0 ? TRUE : FALSE;
				}else
				{
					$data['spr_payment_number'] = ($result + 1);
					$this->db->insert($this->_table,$data);
					$this->set_last_input_id();
					$this->insert_journal_entry($data); // insert to accounting
					return $this->db->affected_rows() > 0 ? TRUE : FALSE;
				}
			}
		}
		
		public function update_enrollment_record($e_id)
		{
			$query = $this->db->select('e_registered')
							  ->where('e_id',$e_id)
							  ->get($this->enrollments_table);
			if($query->num_rows()>=1)
			{
				if($query->row()->e_registered == 1)
				{
					return TRUE;
				}else{
					$this->db->set('e_registered',1)
							 ->where('e_id',$e_id)
							 ->update($this->enrollments_table);
					return $this->db->affected_rows() > 0 ? TRUE : FALSE;
				}
			}
		}
	
		public function get_payment_date($sy_id)
		{
			$sql = "SELECT DISTINCT spr_payment_date 
					FROM student_payment_records 
					WHERE spr_schoolyear_id = ?
					ORDER BY DATE(spr_payment_date)";
			$q = $this->db->query($sql,array($sy_id)); 
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
		}
		
		public function get_payment_list($data)
		{
			$sql = "SELECT student_payment_records.spr_or_no, student_payment_records.spr_ammt_paid, student_payment_records.spr_remarks, student_payment_records.spr_enrollment_id, enrollments.e_profile_id, profiles.firstname, profiles.middlename, profiles.lastname 
					FROM (student_payment_records 
					LEFT JOIN enrollments ON student_payment_records.spr_enrollment_id = enrollments.e_id) 
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id 
					WHERE DATE(student_payment_records.spr_payment_date) = ?
					AND spr_is_deleted = 0
					";
			$q = $this->db->query($sql,array($data)); 
			return $q->num_rows() >= 1 ? $q->result() : FALSE;
		}
		
		public function get_payment_record($id = FALSE)
		{
			if($id !== FALSE)
			{
				$sql = "SELECT spr.spr_or_no,spr.spr_remarks,spr.spr_payment_date,spr.spr_ammt_paid,spr.spr_mode_of_payment as mode
						FROM {$this->_table} spr 
						WHERE spr.spr_id = ? 
						AND spr_is_deleted = 0
						LIMIT 1";
				$query = $this->db->query($sql,array($id));
				return $query->num_rows() >=1 ? $query->row() : FALSE;
			}else{
				$sql = "SELECT spr.spr_or_no,spr.spr_remarks,spr.spr_payment_date,spr.spr_ammt_paid,spr.spr_mode_of_payment as mode
						FROM {$this->_table} spr
						AND spr_is_deleted = 0
						";
				$query = $this->db->query($sql);
				return $query->num_rows() >=1 ? $query->result() : FALSE;			
			}
		}
		
		public function get_payment_record_by_enrollment_id($id)
		{
			$id = intval($id);
			
			$sql = "SELECT `spr_id`,
							`spr_or_no`,
							concat(DATE_FORMAT(`spr_payment_date`,'%M %d, %Y'),' - ',DATE_FORMAT(`spr_created`,'%r') ) as spr_payment_date,
							`spr_ammt_paid`,
							`spr_created`,
							`spr_mode_of_payment`
					FROM `student_payment_records` 
					WHERE `spr_enrollment_id` = ? 
					AND spr_schoolyear_id = ?
					AND spr_is_deleted = 0
					ORDER BY spr_id ASC";
			$query = $this->db->query($sql,array($id,$this->c_sy->id));
			// return ($this->db->last_query());
			
			return $query->num_rows() >=1 ? $query->result() : FALSE;
		}
		
		public function set_last_input_id()
		{
			$this->last_input_id = $this->db->insert_id();
		}
		
		public function get_last_input_id()
		{
			return $this->last_input_id;
		}
		
		public function current_payment_record_id($e_id)
		{
			$sql = "SELECT max(spr_id) as current_spr_id 
					FROM student_payment_records 
					WHERE spr_enrollment_id = ?
					AND spr_is_deleted = 0
					";
			$query = $this->db->query($sql,array($e_id));
			return $query->num_rows() > 0 ? $query->row()->current_spr_id : FALSE;
		}
		
		public function payment_record_ids($e_id,$check)
		{
			// $spr_max = $this->current_payment_record_id($e_id);

			$sql = "SELECT spr_id
					FROM student_payment_records 
					WHERE spr_enrollment_id = ?
					AND spr_is_deleted = 0 ";
			$query = $this->db->query($sql,array($e_id));
			$ids =  $query->num_rows() > 0 ? $query->result() : FALSE;

			if($ids)
			{
				foreach($ids as $k => $v)
				{
					$array[] = $v->spr_id;
				}

				return in_array($check, $array);
			}else{
				return FALSE;
			}

		}
		
		public function current_payment_number($e_id,$spr_id)
		{
			$sql = "SELECT max(spr_payment_number) as current_payment_number 
					FROM student_payment_records 
					WHERE spr_enrollment_id = ? 
					AND spr_is_deleted = 0
					AND spr_id = ?";
			$query = $this->db->query($sql,array($e_id,$spr_id));
			return $query->num_rows() > 0 ? $query->row()->current_payment_number : FALSE;
		}
		
		public function delete_student_payment_and_record($id)
		{
			$sql = "Update student_payment_records 
					set spr_is_deleted = 1, 
					spr_date_deleted = ?, 
					spr_deleted_by = ? 
					WHERE spr_id = ? ";
			$this->db->query($sql,array(NOW,$this->c_user->userid,$id));
			
			if($this->db->affected_rows() >= 1)
			{
				$sql2 = 'DELETE FROM student_payment_breakdown
						 WHERE spb_spr_id = ?';
				$this->db->query($sql2,array($id));
				
				if($this->db->affected_rows() >= 1)
				{
					return TRUE;
				}else{
					log_message('error','Unable to delete student payment breakdown spb_spr_id = '.$id.'');
					return FALSE;
				}
			}else{
				log_message('error','Unable to update student payment record to spr_deleted to 1 spr_id= '.$id.'');
				return FALSE;
			}
		}
		
		
		public function update_payment_record($data,$id)
		{
			$this->db->set($data)
					 ->where('spr_id',$id)
					 ->update($this->_table);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
		
		public function update_fully_paid_status($id)
		{
			$check = "SELECT e_fullypaid
					  FROM enrollments 
					  WHERE e_id = ? 
					  AND e_ay_id = ? ";
					  
			$do_update = "UPDATE enrollments set e_fullypaid = 1
					WHERE e_id = ? 
					AND e_ay_id = ? ";
			$check_query = $this->db->query($check,array($id,$this->c_sy->id));
			
			if($check_query->num_rows() >= 1)
			{
				if($check_query->row()->e_fullypaid == 1)
				{
					return TRUE;
				}else{
					$this->db->query($do_update,array($id,$this->c_sy->id));
				}
			}
		}
		
		public function over_the_counter_payments($data)
		{
			$this->db->insert_batch('fees_over_the_counter',$data);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
		
		public function assessment_fee($data)
		{
			$this->db->insert('assessment_fees',$data);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
		
		public function print_payment_record($id)
		{
			$sql = "SELECT student_payment_records.*,
										 profiles.firstname, 
										 profiles.middlename, 
										 profiles.lastname,
										 profiles.name_ext as nameext,
										 levels.level_desc,
					 					 levels.level_category,
										 enrollments.e_sy_from,
					 					 enrollments.e_sy_to,
					 					 enrollments.e_student_id,
					 					 enrollments.e_profile_id,
					 					 employees.last_name,
						   				 employees.first_name,
						   				 employees.middle_name,
						   			 	 employees.name_ext
							FROM student_payment_records
							LEFT JOIN enrollments ON student_payment_records.spr_enrollment_id = enrollments.e_id
							LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
							LEFT JOIN levels ON levels.level_id = enrollments.e_grade_level
							LEFT JOIN employees ON student_payment_records.spr_user_trans_id = employees.id
							WHERE student_payment_records.spr_id = ? 
							AND student_payment_records.spr_is_deleted = 0";
			$q = $this->db->query($sql,array($id));
			return $q->num_rows() >= 1 ? $q->row() : FALSE;
			/*$sql = "SELECT student_payment_records.spr_or_no,
										 student_payment_records.spr_ammt_paid, 
										 student_payment_records.spr_remarks, 
										 student_payment_records.spr_enrollment_id, 
										 enrollments.e_profile_id, 
										 profiles.firstname, 
										 profiles.middlename, 
										 profiles.lastname,
										 profiles.name_ext
					FROM student_payment_records 
					LEFT JOIN enrollments ON student_payment_records.spr_enrollment_id = enrollments.e_id
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id 
					WHERE student_payment_records.spr_id = ?
					AND student_payment_records.spr_is_deleted = 0 ";
			$q = $this->db->query($sql,array($data)); 
			return $q->num_rows() >= 1 ? $q->result() : FALSE;*/
		}
		
		
		
		
		
		
		public function insert_journal_entry($data)
		{
			if($this->db->table_exists('0_journal_entry'))
			{
				// $input['spr_user_trans_id'] = $this->session->userdata('userid');
				// $input['spr_user_trans_name'] = $this->session->userdata('username');
				// $input['spr_enrollment_id'] = $enrollment_id_post;
				// $input['spr_or_no'] = $this->input->post('spr_or_no');
				// $input['spr_remarks'] = $this->input->post('remarks');
				// $input['spr_payment_date'] =  date('Y-m-d',strtotime($this->input->post('date_of_payment')));
				// $input['spr_schoolyear_id'] = $this->current_school_year;// defined in mycontroller
				// $input['spr_gperiod_id'] = $this->current_grading_period;// defined in mycontroller
				// $input['spr_ammt_paid'] = $this->input->post('ammount_paid');
				// $input['spr_created'] = NOW;
	
				$sql = 'SELECT ';
			
			
			
			}
		}
		
		public function insert_reference()
		{
			
			if($this->db->table_exists('0_gl_trans'))
			{
				$sql = 'SELECT max(type_no]) as max_num';
			
			
			
			
			
			
			
			}
		}
		
		
		
}